package manager;

/**
 * @author huangjm.2012, mohameds.2012, jaslin.wee.2012, huiwensong.2012, sheryl.tay.2012
 * 
 */

import java.sql.SQLException;
import java.util.ArrayList;

import manager.database.DatabaseMgr;
import manager.entitycreator.BidCreator;
import manager.entitycreator.SectionCreator;
import util.ValidationUtil;
import entity.Bid;
import entity.Section;

public class BidMgr {

	private DatabaseMgr dbMgr = new DatabaseMgr();
	private BidCreator bidCreator = new BidCreator();
	private SectionCreator sectionCreator = new SectionCreator();

	public boolean addBid(Bid bid) {

		String sql = "insert into bid (bid_userId, bid_bidAmount, bid_courseCode, bid_sectionCode) "
				+ " values (?,?,?,?)";

		int numRowsAffected = dbMgr.executeUpdate(sql, bid.getStudent()
				.getUserId(), bid.getBidAmount(), bid.getSection().getCourse()
				.getCourseCode(), bid.getSection().getSectionCode());

		return numRowsAffected > 0;
	}

	public int[] addMultipleBids(ArrayList<Bid> bids) {
		String sql = "insert into bid "
				+ " (bid_userId, bid_bidAmount, bid_courseCode, bid_sectionCode) "
				+ " values (?,?,?,?)";

		int[] numRowsAffected = { 0 };
		try {
			dbMgr.open();

			dbMgr.setStatement(sql);
			for (Bid eachBid : bids) {
				dbMgr.addBatch(eachBid.getStudent().getUserId(), eachBid
						.getBidAmount(), eachBid.getSection().getCourse()
						.getCourseCode(), eachBid.getSection().getSectionCode());
			}
			numRowsAffected = dbMgr.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbMgr.close();
		}
		return numRowsAffected;
	}

	public boolean deleteBid(String userId, String courseCode) {

		String sql = "delete from bid where bid_userId=? and bid_courseCode=? ";

		int numRowsAffected = dbMgr.executeUpdate(sql, userId, courseCode);

		return numRowsAffected >= 1;
	}
	
	public boolean deleteBid(String userId, String courseCode,
			String sectionCode) {

		String sql = "delete from bid where bid_userId=? and bid_courseCode=? and bid_sectionCode=?";

		int numRowsAffected = dbMgr.executeUpdate(sql, userId, courseCode,
				sectionCode);

		return numRowsAffected >= 1;
	}

	public void reset() {
		dbMgr.executeUpdate("delete from bid");
	}

	public ArrayList<Bid> retrieveAllBids() {
		String sql = "select * from bids_with_student_section_course_round_with_isopen order by bid_bidAmount desc ";
		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql);
	}

	public ArrayList<Bid> retrieveLastBidInEachSection() {
		String sql = "select * from last_bid_in_each_section_with_details where bidRoundNo=? ";
		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql);
	}

	public Bid retrieveBid(String courseCode, String sectionCode, String userId) {
		String sql = "select * from bids_with_student_section_course_round_with_isopen where bid_userId=? and bid_courseCode=? and bid_sectionCode=?";
		Bid bid = dbMgr.retrieveEntityForQuery(bidCreator, sql, userId,
				courseCode, sectionCode);

		ValidationUtil.checkForElement(bid);

		return bid;
	}

	public Bid retrieveBid(String courseCode, String userId) {
		String sql = "select * from bids_with_student_section_course_round_with_isopen where bid_userId=? and bid_courseCode=?";
		Bid bid = dbMgr.retrieveEntityForQuery(bidCreator, sql, userId,
				courseCode);

		ValidationUtil.checkForElement(bid);

		return bid;
	}

	public ArrayList<Bid> retrieveCurrentRoundBidResultsForSection(String courseCode,
			String sectionCode) {

		String sql = "select distinct * from all_bids_with_all_details "
				+ " where bid_courseCode=? and bid_sectionCode=? and round_roundNo=(select round_roundNo from current_round_with_isopen) "
				+ " order by bid_bidAmount desc, bid_userId asc";

		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql,
				courseCode, sectionCode);
	}

	public ArrayList<Bid> retrievePendingBidsForStudent(String userId) {

		String sql = "select distinct * from all_bids_with_all_details "
				+ " where bid_userId=? and bidResult=? "
				+ " order by bid_courseCode, bid_sectionCode";

		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql, userId,
				Bid.PENDING);
	}

	public ArrayList<Bid> retrieveBidsForSection(String courseCode,
			String sectionCode) {

		String sql = "select * from bids_with_student_section_course_round_with_isopen "
				+ " where bid_courseCode=? and bid_sectionCode=? "
				+ " order by bid_userId ";

		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql,
				courseCode, sectionCode);
	}

	public ArrayList<Bid> retrieveAllBidsForStudent(String userId) {

		String sql = "select distinct * from all_bids_with_all_details "
				+ " where bid_userId=? order by bid_userId ";

		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql, userId);
	}

	public ArrayList<Bid> retrieveBidsForSectionOrderedByHighestBidAmount(
			String courseCode, String sectionCode) {
		String sql = "select * from bids_with_student_section_course_round_with_isopen "
				+ " where bid_courseCode=? and bid_sectionCode=? order by bid_bidAmount desc ";

		return dbMgr.retrieveMultipleEntitiesForQuery(bidCreator, sql, courseCode, sectionCode);
	}

	public ArrayList<Section> retrieveAllSectionsWithBids() {
		String sql = "select * from bids_with_student_section_course_round_with_isopen group by section_courseCode, section_sectionCode order by section_courseCode, section_sectionCode";
		return dbMgr.retrieveMultipleEntitiesForQuery(sectionCreator, sql);
	}

}
